package com.campus.dao.impl;

import com.campus.dao.StudentDao;
import com.campus.dao.UserDao;
import com.campus.model.Student;
import com.campus.model.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Repository
@Slf4j
public class StudentDaoImpl implements StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private UserDao userDao;
    @Override
    public Integer addStudent(Student student) {
        User user = new User();
        user.setName(student.getStu_name());
        user.setPassword("123456");
        user.setUser_id(student.getLogin_id());
        userDao.insert(user);

        String sql=" INSERT INTO t_manager_student (stu_name,from_place,parent_phone,current_grade,is_first,login_id,comment) VALUES " +
                " (?,?,?,?,?,?,?)";
        Object[] args = new Object[]{
                student.getStu_name(),
                student.getFrom_place(),
                student.getParent_phone(),
                student.getCurrent_grade(),
                student.getIs_first(),
                student.getLogin_id(),
                student.getComment(),
        };
        return jdbcTemplate.update(sql,args);

    }

    @Override
    public Integer addStudentByExcel(String excelPath) {
        int rowsInserted = 0;
        String sql = "INSERT INTO t_manager_student (stu_name, from_place, parent_phone, current_grade, is_first, login_id, comment) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (FileInputStream fis = new FileInputStream(excelPath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // Assuming data is in the first sheet
            List<Object[]> batchArgs = new ArrayList<>();
            DataFormatter dataFormatter = new DataFormatter(); // Create a DataFormatter instance

            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // Skip header row if there is one

                String stuName = dataFormatter.formatCellValue(row.getCell(0));
                String fromPlace = dataFormatter.formatCellValue(row.getCell(1));
                String parentPhone = dataFormatter.formatCellValue(row.getCell(2));
                String currentGrade = dataFormatter.formatCellValue(row.getCell(3));
                String isFirst = dataFormatter.formatCellValue(row.getCell(4));
                int loginId = (int) row.getCell(5).getNumericCellValue();
                String comment = dataFormatter.formatCellValue(row.getCell(6));

                //学生自动成为用户
                User user = new User();
                user.setName(stuName);
                user.setPassword("123456");
                user.setUser_id(loginId);
                userDao.insert(user);

                batchArgs.add(new Object[]{stuName, fromPlace, parentPhone, currentGrade, isFirst, loginId, comment});
            }


            rowsInserted = jdbcTemplate.batchUpdate(sql, batchArgs).length;
            System.out.println("Data imported successfully!");

        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowsInserted;
    }

    @Override
    public Integer auditStudent(int studentId) {
        String sql = " UPDATE t_manager_student SET is_audit='1' WHERE id=?";
        return jdbcTemplate.update(sql,studentId);
    }

    @Override
    public Integer batchAuditStudent() {
        String sql = " UPDATE t_manager_student SET is_audit='1' WHERE is_audit='0'";
        return jdbcTemplate.update(sql);
    }
}
